/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package es.usj.dssie.videoclub.dao;

import es.usj.dssie.videoclub.dto.Client;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import es.usj.dssie.videoclub.dto.Movie;
/**
 *
 * @author Nacho
 */
public class MovieJdbcDAO implements MovieDAO {


    public Movie readById(int id) throws DAOException{
         Connection c = null;
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            int i=1;
            c = ConnectionClassGenerator.getConnection();
            statement = c.prepareStatement("select id, name, genre, releasedate, units from movie where id=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setInt(i++, id);
            result = statement.executeQuery();
            Movie obj = null;
            if(result.next()) {
            	i=1;
                obj = new Movie();
                obj.setId(result.getInt(i++));
                obj.setName(result.getString(i++));
               obj.setGenre(result.getString(i++));
               obj.setReleaseDate(result.getDate(i++));
               obj.setUnits(result.getInt(i++));
                return obj;
            } else {
                throw new DAOException("No such instance exception");
            }
        } catch(SQLException sqle) {
            throw new DAOException(sqle);
        }catch (ClassNotFoundException e) {
      	  throw new DAOException(e);
		} finally {
            if(result != null)
                    try { result.close(); } catch(SQLException ignored) { }
            if(statement != null)
                    try { statement.close(); } catch(SQLException ignored) { }
            if(c != null)
                    try { c.close(); } catch(SQLException ignored) { }
        }
    }

    public int readUnits(int id) throws DAOException{
         Connection c = null;
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            int i=1;
            c = ConnectionClassGenerator.getConnection();
            statement = c.prepareStatement("select units from movie where id=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setInt(i++, id);
            result = statement.executeQuery();
            Movie obj = null;
            if(result.next()) {
                i=1;
                obj = new Movie();
                obj.setId(result.getInt(i++));
                obj.setUnits(result.getInt(i++));
                return obj.getUnits();
            } else {
                throw new DAOException("No such instance exception");
            }
        } catch(SQLException sqle) {
            throw new DAOException(sqle);
        }catch (ClassNotFoundException e) {
      	  throw new DAOException(e);
		} finally {
            if(result != null)
                    try { result.close(); } catch(SQLException ignored) { }
            if(statement != null)
                    try { statement.close(); } catch(SQLException ignored) { }
            if(c != null)
                    try { c.close(); } catch(SQLException ignored) { }
        }
    }

    public Movie read(String name) throws DAOException{
         Connection c = null;
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            int i=1;
            c = ConnectionClassGenerator.getConnection();
            statement = c.prepareStatement("select id, name, genre, releasedate, units from movie where name=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setString(i++, name);
            result = statement.executeQuery();
            Movie obj = null;
            if(result.next()) {
            	i=1;
                obj = new Movie();
                obj.setId(result.getInt(i++));
                obj.setName(result.getString(i++));
               obj.setGenre(result.getString(i++));
               obj.setReleaseDate(result.getDate(i++));
               obj.setUnits(result.getInt(i++));
                return obj;
            } else {
                throw new DAOException("No such instance exception");
            }
        } catch(SQLException sqle) {
            throw new DAOException(sqle);
        }catch (ClassNotFoundException e) {
      	  throw new DAOException(e);
		} finally {
            if(result != null)
                    try { result.close(); } catch(SQLException ignored) { }
            if(statement != null)
                    try { statement.close(); } catch(SQLException ignored) { }
            if(c != null)
                    try { c.close(); } catch(SQLException ignored) { }
        }
    }


    public boolean reserveMovie(int id, Client client) throws DAOException, ClassNotFoundException {
        Connection c = null;
        PreparedStatement statement = null;
        try {
            c = ConnectionClassGenerator.getConnection();
            Movie movie = readById(id);
            if(movie != null){
                movie.setUnits(movie.getUnits()-1);
                StringBuffer sb = new StringBuffer("update movie set units=?");
                sb.append(" where id=?");
                statement = c.prepareStatement(sb.toString());
                int i=1;
                statement.setInt(i++, movie.getUnits());
                statement.setInt(i++, movie.getId());
                int n = statement.executeUpdate();
                if(n == 0)
                    return false;
                return true;
            }else return false;
        } catch(SQLException sqle) {
            throw new DAOException(sqle);
        } finally {
            if(statement != null)
                    try { statement.close(); } catch(SQLException ignored) { }
            if(c != null)
                    try { c.close(); } catch(SQLException ignored) { }
        }
    }

}
